package bbs;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import util.DB;

public class CommentDAO {

    private static Comment makeComment(ResultSet rs) throws Exception {
        Comment obj = new Comment();
        obj.setId(rs.getInt("Id"));
        obj.setArticleId(rs.getInt("articleId"));
        obj.setUserId(rs.getInt("userId"));
        obj.setName(rs.getString("name"));
        obj.setWriteTime(rs.getTimestamp("writeTime"));
        obj.setBody(rs.getString("body"));
        return obj;
    }

    public static Comment selectById(int commentId) throws Exception {
        String sql = "EXEC bbs.commentSelectById ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, commentId);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? makeComment(rs) : null;
            }
        }
    }

    public static ArrayList<Comment> select(int articleId) throws Exception {
        String sql = "EXEC bbs.commentSelectAll ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, articleId);
            try (ResultSet rs = stmt.executeQuery()) {
                ArrayList<Comment> list = new ArrayList<Comment>();
                while (rs.next())
                    list.add(makeComment(rs));
                return list;
            }
        }
    }

    public static void insert(int userId, int articleId, String body) throws Exception {
        String sql = "INSERT bbs.[Comment] ([articleId], [userId], [writeTime], [body]) VALUES (?, ?, GETDATE(), ?)";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, articleId);
            stmt.setInt(2, userId);
            stmt.setString(3, body);
            stmt.executeUpdate();
        }
    }

    public static void update(int id, String body) throws Exception {
        String sql = "UPDATE bbs.[Comment] SET [body] = ?, [writeTime] = GETDATE() WHERE [id] = ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setString(1, body);
            stmt.setInt(2, id);
            stmt.executeUpdate();
        }
    }

    public static void delete(int id) throws Exception {
        String sql = "DELETE bbs.[Comment] WHERE [id] = ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, id);
            stmt.executeUpdate();
        }
    }

}
